# coding=UTF-8
import xlrd
import openpyxl
from os import listdir
from os.path import splitext


def write_excel_xlsx(path, value: dict, sheet_name='send_to_song'):
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = sheet_name
    i = 0
    for k, v in value.items():
        i += 1
        sheet.cell(row=i, column=1, value=k)
        sheet.cell(row=i, column=2, value=v)
    workbook.save(path.replace('.xlsx', '.精简副本.xlsx'))
    print("{}表格写入数据成功！".format(path))


def condensed_dictionary(file_path):
    """
    精简掉多余的行数用的，去掉重复值
    :param file_path:完整的xlsx路径
    :return:简化后的字典，用来填充新的表格
    """
    # 打开excel,读取失败是因为xlrd版本过高!
    wb = xlrd.open_workbook(file_path)
    # 按工作簿定位工作表
    sh = wb.sheet_by_name('send_to_song')
    print('元数据行数 ', sh.nrows, end='')  # 有效数据行数
    # print(sh.cell(0, 0).value)  # 输出第一行第一列的值
    # print(sh.row_values(0))  # 输出第一行的所有值
    # 将数据和标题组合成字典
    # 创建新字典,键值不为空,就不写入
    final_dict = {}
    for i in range(sh.nrows):
        if sh.cell(i, 1).value != '' and sh.cell(i, 1).value != ' ':
            final_dict[sh.cell(i, 0).value] = sh.cell(i, 1).value
    print('精简后行数 ', len(final_dict), end='')  # 有效数据行数
    print('精简比', sh.nrows / len(final_dict))
    return final_dict


def get_all_xlsx(path_dir_):
    """
    接受文件夹路径，返回所有xlsx文件路径的列表
    :param path_dir_:
    :return:
    """
    filenames_lst = listdir(path_dir_)
    xlsx_lst_ = []
    for item in filenames_lst:
        if splitext(item)[1] == '.xlsx':
            xlsx_lst_.append('D:\\send_to_song\\{}'.format(str(item)))
    return xlsx_lst_


if __name__ == '__main__':
    filepath = 'D:\\send_to_song\\'
    all_xlsx = get_all_xlsx(filepath)
    for xlsx_path in all_xlsx:
        final_dict = condensed_dictionary(xlsx_path)
        write_excel_xlsx(xlsx_path, final_dict)
